Amazon Redshift Useful SQL: 作成したテーブルやビューの依存関係を確認する

Amazon Redshiftに於いては、テーブルのCREATE及びDROPはRDBMSに比べて割と頻繁に起こり得るアクションなのかなぁ、とAmazon Redshiftを中心としたDWH環境構築に携わって来てみて思っております(テーブル作成直後に適切なデータ型や列圧縮タイプ、分散キー/ソートキーを見つける迄の間等は特に)。テーブルに対するDROP処理を行った際、以下の様にエラーが出る事がありますが、これは対象としているテーブルに対して、何らかの形で依存するような定義、設定を行っている(ために削除出来ない)のがその理由となります。

DROP TABLE public.xxxxxxxx;
ERROR:  cannot drop table xxxxxxxx because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

では、その要因となっている(依存している)テーブルはどれになるんだろう？と言うのが当エントリのテーマです。CASCADEオプション付ければ強制的に削除出来てしまえますが、情況に拠っては事前に対象テーブルがどれなのか、影響範囲がどこまで及ぶのか等については普通に確認しておきたい点でしょう。

当エントリでは、小ネタではありますがその辺り解決のヒントになるポイントをご紹介したいと思います。

目次

• 外部キー(FOREIGN KEY)指定の場合
• VIEW作成による依存の場合
• (おまけ)CASCADEオプションによる強制削除の場合
• (2019/05/09追記)依存関係情報確認用ビュー
• まとめ

外部キー(FOREIGN KEY)指定の場合

外部キーを指定したテーブルを作成していた場合、以下SQL文でその情報を見つける事が出来ます。

• PostgreSQL: Documentation: 9.1: table_constraints

SELECT
  constraint_catalog,
  constraint_schema,
  constraint_name,
  table_catalog,
  table_schema,
  table_name,
  constraint_type,
  is_deferrable,
  initially_deferred
FROM
  information_schema.table_constraints;

では検証してみましょう。まずは削除対象となるテーブルを以下の様に作成してみます。PRIMARY KEYにlistidを指定しています。

create table public.listing (
    listid integer not null distkey,
    sellerid integer not null,
    eventid integer not null,
    dateid smallint not null  sortkey,
    numtickets smallint not null,
    priceperticket decimal(8,2),
    totalprice decimal(8,2),
    listtime timestamp,
    primary key(listid));

次いで、上記テーブルのPRIMARY KEYを外部キーに指定した別テーブルを以下の様に作成します。

create table public.sales (
    salesid integer not null,
    listid integer not null,
    sellerid integer not null,
    buyerid integer not null,
    eventid integer not null encode mostly16,
    dateid smallint not null,
    qtysold smallint not null encode mostly8,
    pricepaid decimal(8,2) encode delta32k,
    commission decimal(8,2) encode delta32k,
    saletime timestamp,
    primary key(salesid),
    foreign key(listid) references listing(listid)
)
distkey(listid)
sortkey(listid,sellerid);

この状態でpublic.listingテーブルの削除を試みます。怒られました。

# DROP TABLE public.listing;
ERROR:  cannot drop table listing because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

所定のSQLを実行してみます。public.salesテーブルがFOREIGN KEYを利用している事が結果からも分かります。

SELECT
    constraint_catalog,
    constraint_schema,
    constraint_name,
    table_catalog,
    table_schema,
    table_name,
    constraint_type,
    is_deferrable,
    initially_deferred
 FROM
    information_schema.table_constraints;
    
 constraint_catalog | constraint_schema |  constraint_name  | table_catalog | table_schema | table_name | constraint_type | is_deferrable | initially_deferred 
--------------------+-------------------+-------------------+---------------+--------------+------------+-----------------+---------------+--------------------
 xxxxxxxxxxx        | public            | listing_pkey      | xxxxxxxxxxx   | public       | listing    | PRIMARY KEY     | NO            | NO
 xxxxxxxxxxx        | public            | sales_listid_fkey | xxxxxxxxxxx   | public       | sales      | FOREIGN KEY     | NO            | NO
 xxxxxxxxxxx        | public            | sales_pkey        | xxxxxxxxxxx   | public       | sales      | PRIMARY KEY     | NO            | NO

改めてpublic.salesテーブルの構成を確認してみます。FOREIGN KEY指定を行っている事が改めて確認出来ました。今回は一連の動作を続けて行い、分かっている前提で進めてみましたが、これが『どのテーブルが怪しいんだろう？』と知らない前提で、となると『どうやらコイツが怪しいようだ』となる訳です。

# \d public.sales
                 Table "public.sales"
   Column   |            Type             | Modifiers 
------------+-----------------------------+-----------
 salesid    | integer                     | not null
 listid     | integer                     | not null
 sellerid   | integer                     | not null
 buyerid    | integer                     | not null
 eventid    | integer                     | not null
 dateid     | smallint                    | not null
 qtysold    | smallint                    | not null
 pricepaid  | numeric(8,2)                | 
 commission | numeric(8,2)                | 
 saletime   | timestamp without time zone | 
Indexes:
    "sales_pkey" PRIMARY KEY, btree (salesid)
Foreign-key constraints:
    "sales_listid_fkey" FOREIGN KEY (listid) REFERENCES listing(listid)

VIEW作成による依存の場合

• DROP TABLE - Amazon Redshift

VIEW作成による依存関係を確認したい場合は、以下のSQL文を使う事で内容を確認する事が出来ます。

select distinct
  c_p.oid as tbloid,
  n_p.nspname as schemaname,
  c_p.relname as name,
  n_c.nspname as refbyschemaname,
  c_c.relname as refbyname,
  c_c.oid as viewoid
from
  pg_catalog.pg_class c_p
    join pg_catalog.pg_depend d_p on c_p.relfilenode = d_p.refobjid
    join pg_catalog.pg_depend d_c on d_p.objid = d_c.objid
    join pg_catalog.pg_class c_c on d_c.refobjid = c_c.relfilenode
    left outer join pg_namespace n_p on c_p.relnamespace = n_p.oid
    left outer join pg_namespace n_c on c_c.relnamespace = n_c.oid
where
  d_c.deptype = 'i'::"char"
  and c_c.relkind = 'v'::"char";

こちらも検証してみましょう。上記FOREIGN KEYの項で使ったlistingテーブルをこちらでも使います。listingテーブルを元にlisting_viewを作成しました。

# CREATE VIEW listing_view AS SELECT * FROM listing;
CREATE VIEW

上記ではsalesテーブルがFOREIGN KEY参照していましたので一旦その原因を除いた上でDROP TABLEを試みます。怒られました。

# DROP TABLE public.sales;
DROP TABLE
# DROP TABLE public.listing;
ERROR:  cannot drop table listing because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

ビュー参照の内容を確認するSQLを流してみます。listing_viewが参照している事が確認出来ました。

# select distinct
  c_p.oid as tbloid,
  n_p.nspname as schemaname,
  c_p.relname as name,
  n_c.nspname as refbyschemaname,
  c_c.relname as refbyname,
  c_c.oid as viewoid
from
  pg_catalog.pg_class c_p
    join pg_catalog.pg_depend d_p on c_p.relfilenode = d_p.refobjid
    join pg_catalog.pg_depend d_c on d_p.objid = d_c.objid
    join pg_catalog.pg_class c_c on d_c.refobjid = c_c.relfilenode
    left outer join pg_namespace n_p on c_p.relnamespace = n_p.oid
    left outer join pg_namespace n_c on c_c.relnamespace = n_c.oid
where
  d_c.deptype = 'i'::"char"
  and c_c.relkind = 'v'::"char"
  and name like'listing%';

  tbloid | schemaname |     name     | refbyschemaname |  refbyname   | viewoid 
--------+------------+--------------+-----------------+--------------+---------
 179352 | public     | listing      | public          | listing_view |  179364
 179364 | public     | listing_view | public          | listing_view |  179364
(2 rows)

問題となっているビューを削除する事で、本来削除したかったテーブル(listing)を削除する事が出来ました。

# DROP VIEW listing_view;
DROP VIEW
# DROP TABLE public.listing;
DROP TABLE

(おまけ)CASCADEオプションによる強制削除の場合

ちなみに、DROP TABLE実行時にCASCADEオプションを以下の様に付与して実行すると、有無を言わさず対象テーブルをドロップする事が可能です。削除時に『このテーブルも併せて消しときましたんで』って出てくればまだアリなのかも知れませんが、特にその辺については何も言及は無い様です。これはこれで便利ですが若干怖いような気もしますね...。

# DROP TABLE public.listing CASCADE;
DROP TABLE

(2019/05/09追記)依存関係情報確認用ビュー

当エントリの内容に関して、より新しい・確実な解法があったので情報を追記します。下記のビューを作成の後、情報を探索する事が可能です。

• sql - cannot drop table users because other objects depend on it - Stack Overflow

DROP VIEW admin.v_view_dependency CASCADE;
CREATE VIEW admin.v_view_dependency AS 
SELECT DISTINCT srcobj.oid AS src_oid
  , srcnsp.nspname AS src_schemaname
  , srcobj.relname AS src_objectname
  , tgtobj.oid AS dependent_viewoid
  , tgtnsp.nspname AS dep_schemaname
  , tgtobj.relname AS dep_objectname
FROM pg_class srcobj
  JOIN pg_depend srcdep ON srcobj.oid = srcdep.refobjid
  JOIN pg_depend tgtdep ON srcdep.objid = tgtdep.objid
  JOIN pg_class tgtobj ON tgtdep.refobjid = tgtobj.oid AND srcobj.oid <> tgtobj.oid
  LEFT JOIN pg_namespace srcnsp ON srcobj.relnamespace = srcnsp.oid
  LEFT JOIN pg_namespace tgtnsp ON tgtobj.relnamespace = tgtnsp.oid
WHERE tgtdep.deptype = 'i'::"char" AND tgtobj.relkind = 'v'::"char";

まとめ

以上、テーブル作成に於ける依存関係の確認方法に関するご紹介でした。規模が大きくなったり、関わっているメンバーが増えたりすると『あれ？このテーブルって...』となることもままあるかと思われます。その際にこれらの情報が原因究明の一助になれば幸いです。こちらからは以上です。

参考情報：

• CREATE TABLE の例 - Amazon Redshift
• DROP TABLE - Amazon Redshift
• Postgresql(psql)でViewビューの定義を調べる方法 - DQNEO起業日記
• DROP TABLE ... CASCADEでビューもろともテーブルを消す - 青木ブログ